package com.example.shipload_e.database_dao;

import org.json.JSONArray;
import org.json.JSONException;

import android.content.Context;
import android.database.Cursor;

public class DBUtil {

	/**
	 * 异常基础数据
	 * @param ctx
	 * @param jsonArray
	 */
	public static void saveTypes(Context ctx,JSONArray jsonArray) {
		try {
			JSONArray ja = jsonArray;
			if(ja.length() > 0) {
				deleteAll(ctx, "TYPES");
				for(int i = 0;i < ja.length();i++) {
					String id = ja.getJSONObject(i).getString("id");
					String name = ja.getJSONObject(i).getString("name");
					String sql = "INSERT INTO TYPES (ID,NAME) VALUES('" + id + "','" + name + "') ";
					BizDBAdapter db = new BizDBAdapter(ctx);
					db.openDBConnection().execSQL(sql);
					db.close();
				}
			}
		} catch (JSONException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 单位基础数据
	 * @param ctx
	 * @param jsonArray
	 */
	public static void saveUnits(Context ctx,JSONArray jsonArray) {
		try {
			JSONArray ja = jsonArray;
			if(ja.length() > 0) {
				deleteAll(ctx, "UNITS");
				for(int i = 0;i < ja.length();i++) {
					String id = ja.getJSONObject(i).getString("id");
					String name = ja.getJSONObject(i).getString("name");
					String sql = "INSERT INTO UNITS (ID,NAME) VALUES('" + id + "','" + name + "') ";
					BizDBAdapter db = new BizDBAdapter(ctx);
					db.openDBConnection().execSQL(sql);
					db.close();
				}
			}
		} catch (JSONException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 插入前删除表格数据
	 */
	public static void deleteAll(Context ctx,String tableName) {
		String sql = "DELETE FROM '" + tableName + "'";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 插入图片数据
	 * @param ctx
	 * @param fpath
	 * @param taskId
	 * @param time
	 */
	public static void insertIntoPhotos(Context ctx,String fpath,String taskId,String time) {
		String sql = "INSERT INTO PHOTOS (ID,FILE_NAME,STATUS,TIME) VALUES('" + taskId + "','"
		        + fpath + "','0','" + time + "') ";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 根据id查询所属图片
	 * @param ctx
	 * @param selectId
	 */
	public static JSONArray selectPhotosInformation(Context ctx,String selectId) {
		String sql = "SELECT * FROM PHOTOS ORDER BY TIME desc";
		BizDBAdapter db = new BizDBAdapter(ctx);
		Cursor cs = db.openDBConnection().rawQuery(sql, new String[]{});
		JSONArray jdatas = com.example.shipload_e.database_dao.ClassConvertBean.returnJsonBeans(cs);
		cs.close();
		db.close();
		return jdatas;
	}

	/**
	 * 提交成功后保存id
	 * @param ctx
	 * @param fpath
	 * @param taskId
	 * @param respondId
	 */
	public static void updatePhotosStatusAndSubmitId(Context ctx,String fpath,String taskId,
	        String respondId) {
		String sql = "UPDATE PHOTOS SET STATUS = '1' ,RESPOND_ID='" + respondId + "' WHERE ID ='"
		        + taskId + "' AND FILE_NAME ='" + fpath + "'";
		BizDBAdapter db = new BizDBAdapter(ctx);
		db.openDBConnection().execSQL(sql);
		db.close();
	}

	/**
	 * 查询未上传成功图数
	 * @param ctx
	 * @param followPastId
	 * @throws JSONException
	 */
	public static String selectCountAboutPhotoSubmitFail(Context ctx,String followPastId)
	        throws JSONException {
		String sql = "SELECT COUNT(*) FAILCOUNT FROM PHOTOS WHERE ID='" + followPastId
		        + "' AND STATUS='0'";
		BizDBAdapter db = new BizDBAdapter(ctx);
		Cursor cs = db.openDBConnection().rawQuery(sql, new String[]{});
		JSONArray jdatas = com.example.shipload_e.database_dao.ClassConvertBean.returnJsonBeans(cs);
		cs.close();
		db.close();
		return jdatas.getJSONObject(0).getString("FAILCOUNT");
	}

	/**
	 * 根据任务id获取上传图片的id
	 * @param ctx
	 * @param followTaskId
	 * @return
	 */
	public static JSONArray selectPhotoRespondByTaskId(Context ctx,String followTaskId) {
		String sql = "SELECT * FROM PHOTOS WHERE ID='" + followTaskId
		        + "' AND STATUS='1'";
		BizDBAdapter db = new BizDBAdapter(ctx);
		Cursor cs = db.openDBConnection().rawQuery(sql, new String[]{});
		JSONArray jdatas = com.example.shipload_e.database_dao.ClassConvertBean.returnJsonBeans(cs);
		cs.close();
		db.close();
		return jdatas;
	}

}
